package com.xk.jdbc;

import javax.xml.namespace.QName;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 增删改查方法
 */
public class TestDaoImpl {

    /**
     * 插入一个Person对象到数据库中
     *
     * @param person 要插入的Person对象
     * @return 插入操作的影响行数，1表示成功，其他表示失败
     */
    //增加
    public int insert(Person person){
        //声明Connection和PreparedStatement变量
        Connection connection;
        PreparedStatement preparedStatement=null;
        // SQL语句，用于插入数据到person表中
        String sql="insert into person(id,name,age,bornDate,email,address)values (?,?,?,?,?,?);";

        // 获取数据库连接
        connection=DBUtils.getConnection();
        try {
            // 准备SQL语句
            preparedStatement =  connection.prepareStatement(sql);
            // 设置SQL语句中的参数
            preparedStatement.setInt(1,person.getId());
            preparedStatement.setString(2,person.getName());
            preparedStatement.setInt(3,person.getAge());
<<<<<<< HEAD
            // bornDate字段暂未使用，设置为null
            preparedStatement.setDate(4,DateUtils.utilToSql(person.getBornDate()));
=======
            preparedStatement.setDate(4,DateUtils.dateToSql(person.getBornDate()));
>>>>>>> 4bee9cde7dbacf9c783c2b85071eba2fff4d0113
            preparedStatement.setString(5,person.getEmail());
            preparedStatement.setString(6,person.getAddress());
            // 执行更新操作
            int result =  preparedStatement.executeUpdate();
            // 根据执行结果输出信息
            if (result == 1){
                System.out.println("新增成功！");
            }else
                System.out.println("新增失败！");
            // 返回影响行数
            return result;
        } catch (SQLException e) {
            // 抛出运行时异常
            throw new RuntimeException(e);
        }finally {
            // 关闭数据库连接和PreparedStatement对象
            DBUtils.closeAll(connection,preparedStatement, null);
        }
    }

    /**
     * 删除指定ID的人员记录
     *
     * @param id 要删除的人员记录的ID
     * @return 返回删除操作的影响行数，1表示删除成功，0表示删除失败
     */
    public int delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

<<<<<<< HEAD
        // 修改SQL语句，使用占位符动态设置ID值
        String sql = "delete from person where id=?";
=======
        // SQL语句，用于删除ID为1的人员记录请注意，这里的SQL语句硬编码了id值为1，这在实际应用中是不合理的，合理的做法是根据方法参数动态设置SQL语句中的ID值
        String sql="delete from person where id=1;";
>>>>>>> 4bee9cde7dbacf9c783c2b85071eba2fff4d0113

        // 链接数据库
        connection = DBUtils.getConnection();

        try {
            // 预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            // 动态设置参数
            preparedStatement.setInt(1, id);

            // 执行更新操作
            int result = preparedStatement.executeUpdate();
            if (result == 1) {
                System.out.println("删除成功！");
            } else {
                System.out.println("删除失败！");
            }
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭数据库连接和PreparedStatement对象，释放资源
            DBUtils.closeAll(connection, preparedStatement, null);
        }
    }

    //修改方法
    public int update(Person person){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        String sql = "update person set name=?,age=?,bornDate=?,email=?,address=? where id=?";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, person.getName());
            preparedStatement.setInt(2, person.getAge());
            preparedStatement.setDate(3, DateUtils.utilToSql(person.getBornDate()));
            preparedStatement.setString(4, person.getEmail());
            preparedStatement.setString(5, person.getAddress());
            preparedStatement.setInt(6, person.getId());
            int result = preparedStatement.executeUpdate();
            if (result == 1) {
                System.out.println("修改成功！");
            } else {
                System.out.println("修改失败!");
            }
            return result;
        } catch (SQLException e) {
            throw new RuntimeException("更新操作失败", e);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, null);
        }
    }

    //查询单个数据
   public Person select(String name) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    Person person = null;

    try {
        // 获取数据库连接
        connection = DBUtils.getConnection();
        // 预处理 SQL 语句
        preparedStatement = connection.prepareStatement("SELECT * FROM person where name=?");
        preparedStatement.setString(1, name);

        // 执行 SQL 语句并获取结果集
        resultSet = preparedStatement.executeQuery();

        // 遍历结果集
        if (resultSet.next()) {
            person = new Person();
            person.setId(resultSet.getInt("id"));
            person.setName(resultSet.getString("name"));
            person.setAge(resultSet.getInt("age"));
            person.setBornDate(resultSet.getDate("bornDate"));
            person.setEmail(resultSet.getString("email"));
            person.setAddress(resultSet.getString("address"));
            return person;
        }
    } catch (SQLException e) {
        // 记录异常日志
        System.err.println("数据库查询失败: " + e.getMessage());
        throw new RuntimeException("查询失败", e);
    } finally {
        // 释放资源
        DBUtils.closeAll(connection,preparedStatement,null);
    }

    return person;
}

//查询所有数据

    /**
     * 实现思路：
     *
     * @return
     */
    public List<Person> selectAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Person> personList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("select * from person");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("bornDate");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");

                Person person = new Person(id, name, age, bornDate, email, address);
                personList.add(person);
            }
            return personList;
        } catch (SQLException e) {
            throw new RuntimeException("查询所有数据失败", e);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }
}
